package com.zcyr.util;

import com.alibaba.druid.util.JdbcUtils;
import com.zcyr.pojo.DatabaseSchemaInfo;
import com.zcyr.pojo.DatabaseTableInfo;
import com.zcyr.pojo.DatabaseEnvironmentInfo;
import com.zcyr.pojo.PgUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

@Component
public class DatabaseTool {

    @Autowired
    DataSource dataSource;

    /**
     * 获取所有表
     * @param schemaName 模式昵称
     * @return 用户创建的所有表
     * @throws SQLException sql异常
     */
    public ArrayList<DatabaseTableInfo> getAllTable(String schemaName) throws SQLException {
        ResultSet rs = null;
        Statement stmt = null;
        try (Connection conn = dataSource.getConnection()) {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(String.format("SELECT * FROM PG_TABLES WHERE schemaname='%s'", schemaName));
            ArrayList<DatabaseTableInfo> list = new ArrayList<>();
            ArrayList<HashMap<String, String>> allData = ResultSetTool.getAllData(rs);
            for (HashMap<String, String> info : allData) {
                list.add(new DatabaseTableInfo(
                        info.get("tablename"),
                        info.get("tableowner"),
                        info.get("schemaname")
                ));
            }
            return list;
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
        }
    }

    /**
     * 获取所有模式
     * @param username schema的owner
     * @return 数据库模式信息
     * @throws SQLException sql异常
     */
    public ArrayList<DatabaseSchemaInfo> getAllSchema(String username) throws SQLException {
        ResultSet rs = null;
        Statement stmt = null;
        try (Connection conn = dataSource.getConnection()){
            stmt = conn.createStatement();
            rs = stmt.executeQuery(String.format(
                    "select schema_name from information_schema.schemata where schema_owner='%s';",
                    username
            ));
            ArrayList<DatabaseSchemaInfo> list = new ArrayList<>();
            ArrayList<HashMap<String, String>> allData = ResultSetTool.getAllData(rs);
            for (HashMap<String, String> info : allData) {
                list.add(new DatabaseSchemaInfo(
                        info.get("schema_name")
                ));
            }
            return list;
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
        }
    }

    public ArrayList<PgUser> getUsers(String where) throws SQLException {
        ResultSet rs = null;
        Statement stmt = null;
        try (Connection conn = dataSource.getConnection()) {
            stmt = conn.createStatement();
            String sql = "SELECT usename, usesysid FROM PG_USER" + (where == null || where.equals("") ? "" : " WHERE " + where);
            rs = stmt.executeQuery(sql);
            ArrayList<HashMap<String, String>> members = ResultSetTool.getAllData(rs);
            ArrayList<PgUser> users = new ArrayList<>();
            for (HashMap<String, String> member : members) {
                users.add(new PgUser(
                        member.get("usesysid"),
                        member.get("usename")
                ));
            }
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            return users;
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
        }
    }

    public ArrayList<PgUser> getUsers() throws SQLException {
        return getUsers(null);
    }
}
